package com.hnkywl.sts.dao.jhgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.dao.base.RegionForBgDao;
import com.hnkywl.sts.entity.jhgl.Bgjhhz;
import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: ZHUCHG
 * Date: 13-6-28
 * Time: 上午10:42
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class BgjhhzDao extends SimpleHibernateTemplate<Bgjhhz, Long> {
    /**
     * 查询保供计划汇总的实体
     */
    public Page<Bgjhhz> findBgjhhzList(String sm, String bm, Date startDate, Date endDate, int pageNo, int pageSize) {
        Page<Bgjhhz> page = new Page<Bgjhhz>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");

        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("lj", "lj", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jyjg", "jyjg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jhdw", "jhdw", CriteriaSpecification.LEFT_JOIN)
//                .createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Projections.sqlProjection("this_.bm as bm", new String[]{"bm"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("sm as sm", new String[]{"sm"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("jhsj as jhsj", new String[]{"jhsj"}, new Type[]{Hibernate.DATE}))
//                        .add(Property.forName("jhsj").as("jhsj"))//计划时间
                        .add(Property.forName("lj.id").as("lj.id"))
                        .add(Property.forName("jyjg.id").as("jyjg.id"))
                        .add(Property.forName("jyjg.qc").as("jyjg.qc")) //物流园
                        .add(Property.forName("jhdw.id").as("jhdw.id"))
                        .add(Property.forName("jhdw.qc").as("jhdw.qc"))  //代理客户
                  //      .add(Property.forName("createUser.id").as("createUser.id"))
                        .add(Property.forName("createUser.xm").as("createUser.xm")) //创建人
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(sm))
            criteria.add(Property.forName("sm").like("%" + sm.trim() + "%", MatchMode.START));
        if (StringUtils.isNotEmpty(bm))
            criteria.add(Property.forName("bm").like("%" + bm.trim() + "%", MatchMode.START));
        if (startDate != null)
            criteria.add(Property.forName("jhsj").ge(startDate));
        if (endDate != null)
            criteria.add(Property.forName("jhsj").lt(endDate));

        RegionDao.createDataRegion(criteria);
        return this.findByCriteria(page, criteria);
    }

    /**
     * 查询计划单位的月度计划是否已经汇总
     *
     * @param jhdwId
     * @return
     */
    public Boolean checkMonthlyPlan(Long jhdwId, Long jhqdId) {
        Calendar cal = Calendar.getInstance();
        int year = cal.get(Calendar.YEAR);
        int month = cal.get(Calendar.MONTH) + 1;
        //每个计划单位的科室每个月只能录入2次计划
        String sql = "select count(id) from t_biz_bgjh  where jhdw_id = ? and jhqd_id=? and jhnf=?  and  jhyf=? and type='ydjh'";
        BigDecimal count = (BigDecimal) createSQLQuery(sql, jhdwId, jhqdId, year, month).uniqueResult();
        Boolean c = count.intValue() >= 2;
        //每个计划单位每个月只能汇总2次计划
        sql = "select count(id) from t_biz_bgjhhz where jhdw_id = ? and jhnf=? and jhyf=? and type='ydjh'";
        count = (BigDecimal) createSQLQuery(sql, jhdwId, year, month).uniqueResult();
        return c || count.intValue() >= 2;
    }

    /**
     * 查询计划单位的月度计划是否已经汇总
     *
     * @param jhdwId
     * @return
     */
    public Boolean checkMonthlyHzPlan(Long jhdwId) {
        Calendar cal = Calendar.getInstance();
        int year = cal.get(Calendar.YEAR);
        int month = cal.get(Calendar.MONTH) + 1;
        String sql = "select count(id) from t_biz_bgjhhz where jhdw_id = ? and jhnf=? and jhyf=? and type='ydjh' and deleted = 0";
        BigDecimal count = (BigDecimal) createSQLQuery(sql, jhdwId, year, month).uniqueResult();
        return count.intValue() >= 2;
    }

    /**
     * 查询计划说明个数
     *
     * @param sm
     * @return
     */
    public Integer checkMonthlyHzPlanSm(String sm) {
        String sql = "select count(id) from t_biz_bgjhhz hz where hz.sm= ? and deleted = 0";
        BigDecimal count = (BigDecimal) createSQLQuery(sql, sm).uniqueResult();
        return count.intValue();
    }

    public Boolean checkBgjh(String sm) {
        String sql = "select 1 " +
                "   from t_biz_bgjh t " +
                "   left join t_biz_bgjhhz hz on t.bgjhhz_id = hz.id " +
                "   where t.sm = ?" +
                "   or hz.sm = ? and t.deleted = 0";
        return existsSQLQuery(sql, sm, sm);
    }


    /*public Integer checkMonthlyPlan(Long jhdwId) {
        Calendar cal = Calendar.getInstance();
        cal.set(Calendar.DAY_OF_MONTH, 1);
        Date start = cal.getTime();
        cal.add(Calendar.MONTH, 1);
        Date end = cal.getTime();
        Long count = (Long) createCriteria(new Criterion[]{
                Restrictions.eq("type", "ydjh"),
                Restrictions.eq("jhdw.id", jhdwId),
                Restrictions.between("dateCreated", start, end)
        }).setProjection(Projections.count("id")).uniqueResult();
        return count.intValue();
    }*/
    public Page<Map> findList(String type, Long jhdwId, Long gyzId, Integer jhnf, Integer jhyf, String wzbm, String sm, Date startDate, Date endDate, String gyzsbcl, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();

        String subHzCondition = StringUtils.EMPTY;
        if (StringUtils.isNotEmpty(wzbm)) {
            subHzCondition += " and h.id in (select mx.bgjhhz_id from t_biz_bgjhhzmx mx,t_sys_gg gg where mx.wzgg_id=gg.id and gg.deleted=0 and mx.deleted=0 and (gg.oldcode like :oldcode))";
            param.put("oldcode", wzbm + "%");
        }
        if (StringUtils.isNotEmpty(gyzsbcl)) {
            subHzCondition += " and h.id in (select mx.bgjhhz_id from t_biz_bgjhhzmx mx where  mx.deleted=0 and mx.sbcl=:sbcl)";
            param.put("sbcl", gyzsbcl);
        }

        if (existsSQLQuery("select 1 from t_sys_yg_bmfw where yg_id = ?", YgService.getCurrentYg().getId())) {
            subHzCondition += " and h.id in (select mx.bgjhhz_id from t_biz_bgjhhzmx mx,v_wzbm_gg_yg_search bmfw  where mx.wzgg_id = bmfw.ggid and bmfw.yg_id=" + YgService.getCurrentYg().getId() + ")";
        } else if (existsSQLQuery("select 1 from v_wzbm_gg_search  where gsid = ?", YgService.getCurrentYgGsId())) {
            subHzCondition += " and h.id in (select mx.bgjhhz_id from t_biz_bgjhhzmx mx, v_wzbm_gg_search bmfw where mx.wzgg_id= bmfw.ggid and bmfw.gsid=" + YgService.getCurrentYgGsId() + ")";
        }


//        String withSql = "with gyzwclsl as " +
//                " (select mx.bgjhhz_id as hzid,count(1) as wclsl from t_biz_bgjhhzmx mx where mx.deleted=0 and mx.zt=1 group by mx.bgjhhz_id) ";
        String sqlColumn = "select d.id as \"id\",d.jhdw as \"jhdw\",d.sm as \"sm\",d.date_created as \"jhny\"," +
                "d.gyz as \"gyz\",d.xm as \"zdr\",d.zt as \"zt\",d.type as \"type\",d.wclsl as \"wclsl\" ";
        String sqlBase = "select h.id,h.jhdw_id,(select j.qc from t_sys_gsjg j where h.jhdw_id=j.id and j.deleted=0) jhdw," +
                " (select g.qc from t_sys_gsjg g where h.gyz_id=g.id and g.deleted=0) gyz,h.sm,h.date_created,h.createuser_id,h.createorgan_id,h.createdepartment_id, " +
                " (select y.xm from t_sys_yg y where h.createuser_id=y.id and y.deleted=0) xm,h.zt,h.type," +
                " nvl((select count(1) as wclsl from t_biz_bgjhhzmx mx where mx.deleted=0 and mx.zt=1 and mx.bgjhhz_id=h.id group by mx.bgjhhz_id),0) wclsl " +
                " from t_biz_bgjhhz h  " +
                " where h.deleted=0 " + subHzCondition;

        if (StringUtils.isNotEmpty(type)) {
            condition.add(" d.type=:type ");
            param.put("type", type);
        }
        if (jhdwId != null) {
            condition.add(" d.jhdw_id=:jhdwId ");
            param.put("jhdwId", jhdwId);
        }
        if (gyzId != null) {
            condition.add(" d.gyz_id=:gyzId ");
            param.put("gyzId", gyzId);
        }
        if (startDate != null) {
            condition.add(" d.date_created>= :startDate ");
            param.put("startDate", startDate);
        }
        if (endDate != null) {
            condition.add(" d.date_created< :endDate ");
            param.put("endDate", endDate);
        }
        if (jhnf != null) {
            condition.add(" d.jhnf=:jhnf ");
            param.put("jhnf", jhnf);
        }
        if (jhyf != null) {
            condition.add(" d.jhyf=:jhyf ");
            param.put("jhyf", jhyf);
        }
        if (StringUtils.isNotEmpty(sm)) {
            condition.add(" d.sm like :sm ");
            param.put("sm", "%" + sm + "%");
        }
        String dataRegion = RegionForBgDao.createSQLDataRegion(null, "d", false);
        if (StringUtils.isNotEmpty(dataRegion))
            condition.add(dataRegion);
        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " where " + StringUtils.join(condition, " and ");
        String orderSql = " order by d.id desc";
        String countSql = " select count(1) from (" + sqlBase + ") d " + whereSql;
        String resultSql = sqlColumn + " from (" + sqlBase + ") d " + whereSql + orderSql;
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    /**
     * 保供物资计划汇总明细
     *
     * @param id
     * @return
     */
    public List<Map> findHzmxList(Long id) {
        // 根据汇总计划id，查询保供计划明细
        String sql = "select mx.id \"id\",\n" +
                "       mx.bgjhhz_id \"bgjhhz.id\",\n" +
                "       mx.wzgg_id \"wzgg.id\",\n" +
                "       gg.code \"wzgg.code\",\n" +
                "       gg.oldcode \"wzgg.oldcode\",\n" +
                "       gg.name \"wzgg.name\",\n" +
                "       gg.ggxh \"wzgg.ggxh\",\n" +
                "       gg.dw \"wzgg.dw\",\n" +
                "       mx.dhrq \"dhrq\",\n" +
                "       mx.jhsl \"jhsl\",\n" +
                "       sz.z \"cd\",\n" +
                "       sz.id \"cd_id\",\n" +
                "       (select sum(kh.sl)\n" +
                "          from t_biz_bgjhhz hz\n" +
                "          left join t_sys_lj lj on hz.lj_id = lj.id\n" +
                "                               and lj.deleted = 0\n" +
                "          left join t_sys_gsjg gyz on lj.gyz_id = gyz.id\n" +
                "                                  and gyz.deleted = 0\n" +
                "          left join t_sys_ck ck on gyz.id = ck.ssgyz_id\n" +
                "                               and ck.deleted = 0\n" +
                "          left join t_biz_kh kh on kh.ck_id = ck.id\n" +
                "                               and kh.deleted = 0\n" +
                "         where hz.id = mx.bgjhhz_id\n" +
                "           and kh.jtwz_id = mx.wzgg_id) \"kcsl\",\n" +
                "       mx.sl1 as \"sl1\",\n" +
                "       mx.sl2 as \"sl2\",\n" +
                "       mx.sbsl as \"sbsl\",\n" +
                "       mx.ph as \"ph\",\n" +
                "       mx.sbcl \"sbcl\",\n" +
                "       mx.bz \"bz\",\n" +
                "       mx.bgjhmx_id \"bgjhmx.id\",\n" +
                "       yg.id \"createUser.id\",\n" +
                "       yg.xm \"jhy\",\n" +
                "       (select max(tg.xm)\n" +
                "          from t_biz_bgjh tb, t_sys_yg tg, t_biz_bgjhhzmx mx\n" +
                "         where tb.bgjhtjr_id = tg.id\n" +
                "           and tb.bgjhhz_id = mx.bgjhhz_id\n" +
                "           and mx.bgjhhz_id = ?) as \"tjr\",\n" +
                "       (select max(tb.bgjhtjrq)\n" +
                "          from t_biz_bgjh tb, t_biz_bgjhhzmx mx\n" +
                "         where tb.bgjhhz_id = mx.bgjhhz_id\n" +
                "           and mx.bgjhhz_id = ?) as \"tjsj\",\n" +
                "       (select tsg.xm\n" +
                "          from t_biz_bgjhhz tz, t_sys_yg tsg\n" +
                "         where tz.bgjhhzr_id = tsg.id\n" +
                "           and tz.id = mx.bgjhhz_id) as \"hzr\",\n" +
                "       (select tz.bgjhhzrq from t_biz_bgjhhz tz where tz.id = mx.bgjhhz_id) as \"hzsj\",\n" +
                "       (select ty.xm\n" +
                "          from t_sys_yg ty, t_biz_bgjhhzmx tmx\n" +
                "         where ty.id = tmx.gyzclr_id\n" +
                "           and tmx.id = mx.id) as \"gyzclr\",\n" +
                "       mx.gyzclrq as \"gyzclsj\",\n" +
                "       (select tys.xm\n" +
                "          from t_sys_yg tys, t_biz_bgjhhzmx tmxs\n" +
                "         where tys.id = tmxs.gyzclr_id\n" +
                "           and tmxs.id = mx.id) as \"ywkclr\",\n" +
                "       mx.ywkclrq as \"ywkclsj\",\n" +
                "       decode(mx.zt,\n" +
                "              1,\n" +
                "              '未处理',\n" +
                "              3,\n" +
                "              '供应站已处理',\n" +
                "              4,\n" +
                "              '业务科已处理',\n" +
                "              5,\n" +
                "              '资金合同已处理',\n" +
                "              6,\n" +
                "              '已经到供应处',\n" +
                "              7,\n" +
                "              '计划科已处理',\n" +
                "              8,\n" +
                "              '采购单已办理') as \"zt\"\n" +
                "  from t_biz_bgjhhzmx mx\n" +
                "  left join t_sys_gg gg on mx.wzgg_id = gg.id\n" +
                "                       and gg.deleted = 0\n" +
                "  left join t_sys_yg yg on mx.createuser_id = yg.id\n" +
                "                       and yg.deleted = 0\n" +
                "  left join t_biz_bgjhmx jhmx on mx.bgjhmx_id = jhmx.id\n" +
                "  left join t_sys_sjzdz sz on sz.id = jhmx.sjzdz_cd_id\n" +
                " where mx.bgjhhz_id = ?\n" +
                "   and mx.deleted = 0\n" +
                " order by gg.code ";
        List<Map> result = createSQLQuery(sql, id, id, id).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }


    public Page<Map> findHzmxListDetail(Long bgjhId, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        String sqlColumn = "select mx.id \"id\", " +
                "       mx.bgjhhz_id \"bgjhhz.id\", " +
                "       mx.wzgg_id \"wzgg.id\", " +
                "       gg.code \"wzgg.code\", " +
                "       gg.oldcode \"wzgg.oldcode\"," +
                "       gg.name \"wzgg.name\"," +
                "       gg.cz||' '||gg.ggxh \"wzgg.ggxh\"," +
                "       gg.th \"wzgg.th\"," +
                "       gg.dw \"wzgg.dw\"," +
                "       mx.dhrq \"dhrq\"," +
                "       mx.jhsl \"jhsl\"," +
                "       sz.z \"cd\"," +
                "       sz.id \"cd_id\"," +
                " NVL((select sum(kh.sl) from t_biz_bgjhhz hz " +
                " left join t_biz_kh kh on kh.ck_id=hz.dhck_id and kh.deleted=0 " +
                " where hz.id= mx.bgjhhz_id and kh.jtwz_id=mx.wzgg_id),0) \"kcsl\"," +
                "       mx.sl1 as \"sl1\"," +
                "       mx.sl2 as \"sl2\"," +
                "       mx.sbsl as \"sbsl\"," +
                "       mx.ph as \"ph\"," +
                "       mx.sbcl \"sbcl\"," +
                "       mx.bz \"bz\"," +
                "       mx.bgjhmx_id \"bgjhmx.id\"," +
                "       yg.id \"createUser.id\"," +
                "       yg.xm \"jhy\"," +
                "       (select max(tg.xm)" +
                "          from t_biz_bgjh tb, t_sys_yg tg, t_biz_bgjhhzmx mx" +
                "         where tb.bgjhtjr_id = tg.id" +
                "           and tb.bgjhhz_id = mx.bgjhhz_id" +
                "           and mx.bgjhhz_id = :id) as \"tjr\"," +
                "       (select max(tb.bgjhtjrq)" +
                "          from t_biz_bgjh tb, t_biz_bgjhhzmx mx" +
                "         where tb.bgjhhz_id = mx.bgjhhz_id" +
                "           and mx.bgjhhz_id = :id) as \"tjsj\"," +
                "       (select tsg.xm" +
                "          from t_biz_bgjhhz tz, t_sys_yg tsg" +
                "         where tz.bgjhhzr_id = tsg.id" +
                "           and tz.id = mx.bgjhhz_id) as \"hzr\"," +
                "       (select tz.bgjhhzrq from t_biz_bgjhhz tz where tz.id = mx.bgjhhz_id) as \"hzsj\"," +
                "       (select ty.xm" +
                "          from t_sys_yg ty" +
                "         where ty.id = mx.gyzclr_id" +
                "           ) as \"gyzclr\"," +
                "       mx.gyzclrq as \"gyzclsj\"," +
                "       (select tys.xm" +
                "          from t_sys_yg tys" +
                "         where tys.id = mx.ywkclr_id" +
                "           ) as \"ywkclr\"," +
                "       mx.ywkclrq as \"ywkclsj\"," +
                "(case when mx.cgfs=1 then '比价' when mx.cgfs=0 then '招标' else '' end) as \"cgfs\"," +
                "(select WMSYS.WM_CONCAT(cgdmx.cgd_id) from T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where  cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and cgdmx.deleted!=1) \"cgdid\"," +
                "(select WMSYS.WM_CONCAT(cgd.cgdh) from T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and cgdmx.deleted!=1 and cgd.deleted!=1) \"cgdh\"," +
                "(select WMSYS.WM_CONCAT(yg.xm) from T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz,T_SYS_YG yg where cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and yg.id=cgd.YSR_ID and cgdmx.deleted!=1 and cgd.deleted!=1) \"ysr\"," +
                "(select cgd.ysrq from T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and cgdmx.deleted!=1 and cgd.deleted!=1 and rownum=1) \"yssj\"," +
                "(case when mx.cgfs=1 then " +
                "(select WMSYS.WM_CONCAT(htmx.ht_id) from T_BIZ_HTMX htmx,T_BIZ_HTJHDZ htdz where htmx.id=htdz.bghtmxid and htdz.detailid=mx.id)" +
                " else (select WMSYS.WM_CONCAT(cgd.cght_id) from T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and cgdmx.deleted!=1 and cgd.deleted!=1) end) as \"htid\"," +
                "(case when mx.cgfs=1 then " +
                "(select WMSYS.WM_CONCAT(ht.htbm) from T_BIZ_HT ht,T_BIZ_HTMX htmx,T_BIZ_HTJHDZ htdz where ht.id=htmx.ht_id and htmx.id=htdz.bghtmxid and htdz.detailid=mx.id and htmx.deleted!=1 and ht.deleted!=1)" +
                " else (select WMSYS.WM_CONCAT(ht.htbm) from T_BIZ_HT ht,T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where ht.id=cgd.cght_id and cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and ht.deleted!=1 and cgdmx.deleted!=1 and cgd.deleted!=1) end) as \"htbm\"," +
                "       decode(mx.zt," +
                "              1,'供应站未处理'," +
                "              3,'供应站已处理'," +
                "              4,'业务科已处理'," +
                "              5,'计划正在导入'," +
                "              6,'已经提交到物供'," +
                "              7,'计划科已处理'," +
                "              8,'采购单已办理'," +
                "              10,'已退回') as \"zt\"";
        String sqlBase = "  from t_biz_bgjhhzmx mx " +
                "  left join t_sys_gg gg on mx.wzgg_id = gg.id and gg.deleted = 0 " +
                "  left join t_sys_yg yg on mx.createuser_id = yg.id and yg.deleted = 0 " +
                "  left join t_biz_bgjhmx jhmx on mx.bgjhmx_id = jhmx.id " +
                "  left join t_sys_sjzdz sz on sz.id = jhmx.sjzdz_cd_id " +
                " where mx.bgjhhz_id = :id and mx.deleted = 0 " +
                " order by gg.oldcode ";

        String countSql = "select count(1) from (" + sqlColumn + sqlBase + ")";
        String resultSql = sqlColumn + " " + sqlBase;
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setParameter("id", bgjhId).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setParameter("id", bgjhId)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    /**
     * 查询合同信息，估计计划汇总明细id
     *
     * @param id
     * @return
     */
    public List<Map> findHtxxList(Long id) {
        String sql = "select gg.code \"wzgg.code\",gg.oldcode \"wzgg.oldcode\",gg.name \"wzgg.name\",gg.ggxh \"wzgg.ggxh\",gg.dw \"wzgg.dw\"," +
                "       x.htbm \"hth\",x.htqdr \"qdr\",x.qdrq \"qdrq\",x.bz \"bz\" " +
                "      from t_biz_bgjhhzmx hzmx " +
                "       inner join t_sys_gg gg on hzmx.wzgg_id=gg.id " +
                "       left join (select dz.detailid as hzmxid,ht.id as htid,ht.htbm,ht.htqdr,ht.qdrq,ht.bz " +
                "       from t_biz_htjhdz dz  " +
                "       left join t_biz_htmx htmx on dz.bghtmxid=htmx.id " +
                "       left join t_biz_ht ht on ht.id=htmx.ht_id " +
                "       where htmx.deleted=0 and ht.deleted=0) x on hzmx.id=x.hzmxid " +
                "   where hzmx.bgjhhz_id = ?" +
                "   order by gg.code";
        List<Map> result = createSQLQuery(sql, id).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }

    //分頁查詢合同明細
    public Page<Map> findHtxxLists(Long id, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        String sqlColumn = "select gg.code \"wzgg.code\",gg.oldcode \"wzgg.oldcode\",gg.name \"wzgg.name\",gg.ggxh \"wzgg.ggxh\",gg.dw \"wzgg.dw\"," +
                "       x.htbm \"hth\",x.htqdr \"qdr\",x.qdrq \"qdrq\",x.bz \"bz\" ";
        String sqlBase = " from t_biz_bgjhhzmx hzmx " +
                "  inner join t_sys_gg gg on hzmx.wzgg_id=gg.id " +
                "  left join (select dz.detailid as hzmxid,ht.id as htid,ht.htbm,ht.htqdr,ht.qdrq,ht.bz " +
                "  from t_biz_htjhdz dz  " +
                "  left join t_biz_htmx htmx on dz.bghtmxid=htmx.id " +
                "  left join t_biz_ht ht on ht.id=htmx.ht_id " +
                "  where htmx.deleted=0 and ht.deleted=0) x on hzmx.id=x.hzmxid " +
                "  where hzmx.bgjhhz_id = :id" +
                "  order by gg.code";

        String countSql = "select count(1) from (" + sqlColumn + sqlBase + ")";
        String resultSql = sqlColumn + " " + sqlBase;
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setParameter("id", id).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setParameter("id", id)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    /**
     * 查询采购单信息，估计计划汇总明细id
     *
     * @param id
     * @return
     */
    public List<Map> findCgdxxList(Long id) {
        String sql = "select gg.code \"wzgg.code\",gg.oldcode \"wzgg.oldcode\",gg.name \"wzgg.name\",gg.ggxh \"wzgg.ggxh\",gg.dw \"wzgg.dw\", " +
                "       x.cgdh \"cgdh\",x.cgsj \"kdrq\",x.rksj \"rkrq\" " +
                "      from t_biz_bgjhhzmx hzmx " +
                "       inner join t_sys_gg gg on hzmx.wzgg_id=gg.id " +
                "       left join (select dz.detailid as hzmxid,cgd.id as cgdid,cgd.cgdh,cgd.cgsj,cgd.rksj " +
                "       from t_biz_htjhdz dz  " +
                "       left join t_biz_htmx htmx on dz.bghtmxid=htmx.id " +
                "       left join t_biz_ht ht on ht.id=htmx.ht_id " +
                "       left join t_biz_cgd cgd on cgd.cght_id=ht.id " +
                "       where htmx.deleted=0 and ht.deleted=0 and cgd.deleted=0) x on hzmx.id=x.hzmxid " +
                "   where hzmx.bgjhhz_id = ? " +
                "   order by gg.code";
        List<Map> result = createSQLQuery(sql, id).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }

    public Page<Map> findCgdxxLists(Long id, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        String sqlColumn = "select gg.code \"wzgg.code\",gg.oldcode \"wzgg.oldcode\",gg.name \"wzgg.name\",gg.ggxh \"wzgg.ggxh\",gg.dw \"wzgg.dw\", " +
                "  x.cgdh \"cgdh\",x.cgsj \"kdrq\",x.rksj \"rkrq\" ";
        String sqlBase = "from t_biz_bgjhhzmx hzmx " +
                " inner join t_sys_gg gg on hzmx.wzgg_id=gg.id " +
                " left join (select dz.detailid as hzmxid,cgd.id as cgdid,cgd.cgdh,cgd.cgsj,cgd.rksj " +
                " from t_biz_htjhdz dz  " +
                " left join t_biz_htmx htmx on dz.bghtmxid=htmx.id " +
                " left join t_biz_ht ht on ht.id=htmx.ht_id " +
                " left join t_biz_cgd cgd on cgd.cght_id=ht.id " +
                " where htmx.deleted=0 and ht.deleted=0 and cgd.deleted=0) x on hzmx.id=x.hzmxid " +
                " where hzmx.bgjhhz_id = :id " +
                " order by gg.code";

        String countSql = "select count(1) from (" + sqlColumn + sqlBase + ")";
        String resultSql = sqlColumn + " " + sqlBase;
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setParameter("id", id).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setParameter("id", id)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

//    public Integer checkDescription(String desc){
//
//    }
}
